package com.example.demo.api.sql;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import com.example.demo.api.constant.*;
import com.example.demo.api.entity.*;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.*;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.*;

import java.io.StringReader;
import java.util.ArrayList;
import java.util.List;
import java.util.Stack;


@Slf4j
@Data
public class CommonQueryService {

    /**
     * schema名称
     */
    private static String schemaName = "test";

    /**
     * 是否使用AS关键字
     */
    public static final boolean useAs = true;

    public static String getSql(QuerySqlEntity queryEntity) {
        PlainSelect select = getSelect(queryEntity);
        return select.toString();
    }

    public static PlainSelect getSelect(QuerySqlEntity queryEntity) {
        PlainSelect plainSelect = new PlainSelect();
        // 主表
        Table mainTable = getTable(schemaName, queryEntity.getSourceTable(), queryEntity.getSourceTableAlias(), useAs);
        plainSelect.setFromItem(mainTable);
        // JOIN条件
        List<Join> joinList = getJoinList(queryEntity.getJoins());
        plainSelect.setJoins(joinList);
        // 行数
        Long limitNumber = queryEntity.getLimit();
        plainSelect.setLimitBy(getLimit(limitNumber));
        // WHERE条件
        Expression whereExpression = getFilterExpression(queryEntity.getFilters());
        plainSelect.setWhere(whereExpression);
        // 分组条件
        List<Column> groupByColumnList = getGroupByColumnList(queryEntity.getGroups());
        for (Column column : groupByColumnList) {
            plainSelect.addGroupByColumnReference(column);
        }
        // 排序条件
        List<OrderByElement> orderByElementList = getOrderByElementList(queryEntity.getOrders());
        plainSelect.addOrderByElements(orderByElementList);
        // 设置SelectItem
        List<SelectItem<?>> selectColList = getSelectItemList(queryEntity.getFields());
        plainSelect.addSelectItems(selectColList);
        List<SelectItem<?>> selectAggregateColList = getAggregateSelectItemList(queryEntity.getAggregates());
        plainSelect.addSelectItems(selectAggregateColList);
        for (JoinEntity join : queryEntity.getJoins()) {
            List<SelectItem<?>> selectJoinTableColList = getSelectItemList(join.getFields());
            plainSelect.addSelectItems(selectJoinTableColList);
        }
        return plainSelect;
    }


    /**
     * 构造jsqlparser的Table对象
     *
     * @param schemaName
     * @param tableName
     * @param tableAlias
     * @param useAs
     * @return
     */
    public static Table getTable(String schemaName, String tableName, String tableAlias, boolean useAs) {
        Table table = new Table(schemaName, tableName);
        if (StrUtil.isNotEmpty(tableAlias)) {
            table.setAlias(new Alias(tableAlias, useAs));
        }
        return table;
    }

    /**
     * 构造jsqlparser的Column对象
     *
     * @param fieldEntity
     * @return
     */
    public static Column getColumn(FieldEntity fieldEntity) {
        return getColumn(schemaName, fieldEntity.getTable(), fieldEntity.getTableAlias(), fieldEntity.getColName());
    }

    /**
     * 构造jsqlparser的Column对象
     *
     * @param schemaName
     * @param tableName
     * @param tableAlias
     * @param colName
     * @return
     */
    public static Column getColumn(String schemaName, String tableName, String tableAlias, String colName) {
        Table table = getTable(schemaName, tableName, tableAlias, useAs);
        return new Column(table, colName);
    }

    /**
     * 构造jsqlparser的Column对象集合
     *
     * @param groupEntityList
     * @return
     */
    public static List<Column> getGroupByColumnList(List<GroupEntity> groupEntityList) {
        if (CollectionUtil.isEmpty(groupEntityList)) {
            return new ArrayList<>();
        }
        List<Column> columnList = new ArrayList<>();
        for (GroupEntity groupEntity : groupEntityList) {
            FieldEntity field = groupEntity.getField();
            Column column = getColumn(schemaName, field.getTable(), field.getTableAlias(), field.getColName());
            columnList.add(column);
        }
        return columnList;
    }

    /**
     * 构造jsqlparser的Join对象
     *
     * @param joinEntity
     * @return
     */
    public static Join getJoin(JoinEntity joinEntity) {
        Join join = new Join();
        Table joinTable = getTable(schemaName, joinEntity.getTable(), joinEntity.getTableAlias(), useAs);
        join.setRightItem(joinTable);
        String joinType = joinEntity.getType();
        switch (joinType) {
            case JoinType.INNER_JOIN:
                join.setInner(true);
                break;
            case JoinType.LEFT_JOIN:
                join.setLeft(true);
                break;
            case JoinType.RIGHT_JOIN:
                join.setRight(true);
                break;
            case JoinType.FULL_JOIN:
                join.setFull(true);
                break;
            default:
                log.error("不支持的联接方式: {}", joinType);
                throw new IllegalArgumentException("不支持的联接方式: " + joinType);
        }
        FieldEntity field = joinEntity.getCondition().getField();
        FieldEntity joinedField = joinEntity.getCondition().getJoinedField();
        Column leftColumnEntity = getColumn(field);
        Column rightColumnEntity = getColumn(joinedField);
        Expression onExpression = new EqualsTo(leftColumnEntity, rightColumnEntity);
        join.addOnExpression(onExpression);
        return join;
    }

    /**
     * 构造jsqlparser的Join对象集合
     *
     * @param joinEntityList
     * @return
     */
    public static List<Join> getJoinList(List<JoinEntity> joinEntityList) {
        if (CollectionUtil.isEmpty(joinEntityList)) {
            return new ArrayList<>();
        }
        List<Join> joinList = new ArrayList<>();
        for (JoinEntity joinEntity : joinEntityList) {
            Join join = getJoin(joinEntity);
            joinList.add(join);
        }
        return joinList;
    }


    /**
     * 将包含多个逻辑条件的SqlMultiExpression转为jsqlparser的Expression
     *
     * @param filterEntityList
     * @return
     */
    public static Expression getFilterExpression(List<FilterEntity> filterEntityList) {
        // 这部分逻辑参考中缀表达式的计算方法
        Stack<Expression> conditionObjectStack = new Stack<>();
        Stack<FilterEntity> joinObjectStack = new Stack<>();

        for (FilterEntity filterEntity : filterEntityList) {
            String filterType = filterEntity.getType();
            switch (filterType) {
                case FilterType.LEFT_PARENTHESIS:
                    joinObjectStack.push(filterEntity);
                    break;
                case FilterType.RIGHT_PARENTHESIS:
                    while (true) {
                        FilterEntity pop = joinObjectStack.pop();
                        if (FilterType.LEFT_PARENTHESIS.equals(pop.getType())) {
                            Expression expression = conditionObjectStack.pop();
                            Expression parenthesizedOrExpression = new Parenthesis(expression);
                            conditionObjectStack.push(parenthesizedOrExpression);
                            break;
                        } else if (FilterType.AND.equals(pop.getType())) {
                            Expression rightExpression = conditionObjectStack.pop();
                            Expression leftExpression = conditionObjectStack.pop();
                            Expression expression = new AndExpression(leftExpression, rightExpression);
                            conditionObjectStack.push(expression);
                        } else if (FilterType.OR.equals(pop.getType())) {
                            Expression rightExpression = conditionObjectStack.pop();
                            Expression leftExpression = conditionObjectStack.pop();
                            Expression expression = new OrExpression(leftExpression, rightExpression);
                            conditionObjectStack.push(expression);
                        }
                    }
                    break;
                case FilterType.AND:
                    joinObjectStack.push(filterEntity);
                    break;
                case FilterType.OR:
                    if (joinObjectStack.isEmpty()) {
                        joinObjectStack.push(filterEntity);
                    } else {
                        FilterEntity peek = joinObjectStack.peek();
                        if (FilterType.AND.equals(peek.getType())) {
                            // AND
                            joinObjectStack.pop();
                            Expression rightExpression = conditionObjectStack.pop();
                            Expression leftExpression = conditionObjectStack.pop();
                            Expression expression = new AndExpression(leftExpression, rightExpression);
                            joinObjectStack.push(filterEntity);
                            conditionObjectStack.push(expression);
                        } else {
                            joinObjectStack.push(filterEntity);
                        }
                    }

                    break;
                case FilterType.CONDITION:
                    Expression conditionExp = FilterEntityToExpression(filterEntity);
                    conditionObjectStack.push(conditionExp);
                    break;
                default:
                    log.error("不支持的FilterType: {}", filterType);
                    throw new IllegalArgumentException("不支持的FilterType: " + filterType);
            }
        }

        while (!joinObjectStack.isEmpty()) {
            FilterEntity pop = joinObjectStack.pop();
            if (FilterType.AND.equals(pop.getType())) {
                Expression rightExpression = conditionObjectStack.pop();
                Expression leftExpression = conditionObjectStack.pop();
                Expression expression = new AndExpression(leftExpression, rightExpression);
                conditionObjectStack.push(expression);
            } else if (FilterType.OR.equals(pop.getType())) {
                Expression rightExpression = conditionObjectStack.pop();
                Expression leftExpression = conditionObjectStack.pop();
                Expression expression = new OrExpression(leftExpression, rightExpression);
                conditionObjectStack.push(expression);
            }
        }

        return conditionObjectStack.pop();
    }

    public static Expression FilterEntityToExpression(FilterEntity filterEntity) {
        FieldEntity field = filterEntity.getField();
        Column column = getColumn(field);
        String operator = filterEntity.getOperator();
        switch (operator) {
            case OperatorType.OPERATOR_EQ:
                return FilterExpressionHandler.handlerEQ(filterEntity);
            case OperatorType.OPERATOR_NE:
                return FilterExpressionHandler.handlerNE(filterEntity);
            case OperatorType.OPERATOR_GT:
                return FilterExpressionHandler.handlerGT(filterEntity);
            case OperatorType.OPERATOR_LT:
                return FilterExpressionHandler.handlerLT(filterEntity);
            case OperatorType.OPERATOR_GE:
                return FilterExpressionHandler.handlerGE(filterEntity);
            case OperatorType.OPERATOR_LE:
                return FilterExpressionHandler.handlerLE(filterEntity);
            case OperatorType.OPERATOR_LIKE:
                return FilterExpressionHandler.handlerLIKE(filterEntity);
            case OperatorType.OPERATOR_BETWEEN:
                return FilterExpressionHandler.handlerBETWEEN(filterEntity);
            case OperatorType.OPERATOR_IN:
                return FilterExpressionHandler.handlerIN(filterEntity);
            case OperatorType.OPERATOR_IS_NULL:
                return FilterExpressionHandler.handlerISNULL(filterEntity);
            default:
                log.error("不支持的比较运算符: {}", operator);
                throw new IllegalArgumentException("不支持的比较运算符: " + operator);
        }
    }

    /**
     * 构造jsqlparser的SelectItem对象
     *
     * @param fieldEntity
     * @return
     */
    public static SelectItem<?> getSelectItem(FieldEntity fieldEntity) {
        Column column = getColumn(fieldEntity);
        return new SelectItem<>(column);
    }

    /**
     * 构造jsqlparser的SelectItem对象
     *
     * @param aggregateEntity
     * @return
     */
    public static SelectItem<?> getAggregateSelectItem(AggregateEntity aggregateEntity) {

        // 获取聚合函数名称
        String aggregateFunctionName = aggregateEntity.getAggFunc();
        // 获取要聚合的列名
        List<Column> columnList = new ArrayList<>();
        Column column = getColumn(aggregateEntity.getField());
        columnList.add(column);

        // 创建Function对象
        Function function = new Function();
        function.setName(aggregateFunctionName);
        function.setParameters(columnList.toArray(new Expression[0]));

        // 创建SelectItem对象
        SelectItem<?> selectItem = new SelectItem<>(function);
        return selectItem;
    }

    /**
     * 构造jsqlparser的SelectItem对象集合
     *
     * @param fieldEntityList
     * @return
     */
    public static List<SelectItem<?>> getSelectItemList(List<FieldEntity> fieldEntityList) {
        if (CollectionUtil.isEmpty(fieldEntityList)) {
            return new ArrayList<>();
        }
        List<SelectItem<?>> selectItemList = new ArrayList<>();
        for (FieldEntity fieldEntity : fieldEntityList) {
            SelectItem<?> selectItem = getSelectItem(fieldEntity);
            selectItemList.add(selectItem);
        }
        return selectItemList;
    }

    /**
     * 根据聚合函数字段，构造jsqlparser的SelectItem对象集合
     *
     * @param aggregateEntityList
     * @return
     */
    public static List<SelectItem<?>> getAggregateSelectItemList(List<AggregateEntity> aggregateEntityList) {
        if (CollectionUtil.isEmpty(aggregateEntityList)) {
            return new ArrayList<>();
        }
        List<SelectItem<?>> selectItemList = new ArrayList<>();
        for (AggregateEntity aggregateEntity : aggregateEntityList) {
            SelectItem<?> aggregateSelectItem = getAggregateSelectItem(aggregateEntity);
            selectItemList.add(aggregateSelectItem);
        }
        return selectItemList;
    }


    /**
     * 构造jsqlparser的OrderByElement对象集合
     *
     * @param orderEntity
     * @return
     */
    public static OrderByElement getOrderByElement(OrderEntity orderEntity) {
        Column column = getColumn(orderEntity.getField());
        OrderByElement orderByElement = new OrderByElement();
        orderByElement.setExpression(column);
        String order = orderEntity.getOrder();
        switch (order) {
            case OrderType.ASC:
                orderByElement.setAsc(true);
                break;
            case OrderType.DESC:
                orderByElement.setAsc(false);
                break;
            default:
                log.error("不支持的排序规则: {}", order);
                throw new IllegalArgumentException("不支持的排序规则: " + order);
        }
        return orderByElement;
    }


    /**
     * 构造jsqlparser的OrderByElement对象集合
     *
     * @param orderEntityList
     * @return
     */
    public static List<OrderByElement> getOrderByElementList(List<OrderEntity> orderEntityList) {
        if (CollectionUtil.isEmpty(orderEntityList)) {
            return new ArrayList<>();
        }
        List<OrderByElement> orderByElementList = new ArrayList<>();
        for (OrderEntity orderEntity : orderEntityList) {
            OrderByElement orderByElement = getOrderByElement(orderEntity);
            orderByElementList.add(orderByElement);
        }
        return orderByElementList;
    }


    /**
     * 构造jsqlparser的Limit对象
     *
     * @param limitNumber
     * @return
     */
    public static Limit getLimit(Long limitNumber) {
        Limit limit = new Limit();
        limit.setRowCount(new LongValue(limitNumber));
        return limit;
    }


}
